Imports System.Configuration
Imports System.Data
Imports System.Xml
Imports System.IO
Imports System.Collections.Generic
Imports System.ComponentModel
Imports visAd.ExceptionHandler
Imports Microsoft.ConfigurationManagement.ManagementProvider
Imports Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine
Imports Microsoft.ConfigurationManagement.ManagementProvider.ConnectionManagerBase
Imports System.Text


Namespace visAd.Providers

    Public Class vaSCCMProvider
        'Dim ds As New System.Data.DataSet
        Dim col_name As New ArrayList
        Dim col_sname As New ArrayList
        Dim xnodelistf As XmlNodeList
        Dim xnodelistc As XmlNodeList
        Dim xnodelista As XmlNodeList
        Private Const mClassName = "SCCMProvider"
        Public Function discoverServers(ByVal strServerType As String) As System.Collections.ArrayList 'Implements Global.visAd.Framework.vaInterface.discoverServers
            Dim s = ""
            discoverServers = s
        End Function

        Public Function setDataset(ByVal dsVar As System.Data.DataSet) As Boolean 'Implements Global.visAd.Framework.vaInterface.setDataset

        End Function

        Public Function setFilter(ByVal strFilterXML As String) As Boolean 'Implements Global.visAd.Framework.vaInterface.setFilter

        End Function
        Private Function getconnection(ByVal sysname As String) As WqlConnectionManager
            '-----------------------Connecting to Services for SCCM Server------------------
            Dim namedValues As New SmsNamedValuesDictionary
            Dim con As New WqlConnectionManager(namedValues)

            Try

                'Dim connstring = vaCustomException.SCCMConnectionString
                ' Dim connstring = "Domain=phx;UserName=Administrator;password=p@ssw0rd"
                'Dim connsubstring(3) As String
                'Dim splitter = ";'"
                'connsubstring = connstring.Split(splitter)
                'Dim domain = connsubstring(0).Replace("Domain=", "")
                'Dim user = connsubstring(1).Replace("UserName=", "")
                'Dim Password = connsubstring(2).Replace("password=", "")
                'Dim con_param = CStr(domain) + "\" + user
                'If (System.Net.Dns.GetHostName().ToUpper() = sysname.ToUpper()) Then
                con.Connect(sysname)
                ' Else
                'con.Connect(sysname, "MSBUSC\Administrator", "msbusc@123")
                ' con.Connect(sysname, con_param, Password)
                ' End If
                getconnection = con
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getData")
                Throw vaEx
            Catch cnEx As ConfigurationErrorsException
                Throw New vaCustomException(cnEx, mClassName, "getconnection", "User does not have access permission.")
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getconnection", "Unable to connect - " + ex.Message)
            End Try

            '-----------------
        End Function
        Public Function getData(ByVal filterxml As String) As DataSet
            '------------------ interface for visAd ------------------
            Dim ar As New ArrayList
            Dim da As New DataSet
            Dim con As WqlConnectionManager
            Dim sysname = ""
            Dim i = 0
            Dim xdoc As New XmlDocument
            Try
                xdoc.LoadXml(filterxml)
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getData")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getData", "visAd data not in correct format")
            End Try
            ar = getVisServer(xdoc, "SCCM")
            '--------------loop for servers to get data------------
            For i = 0 To ar.Count - 1
                sysname = ar(i)
                Try
                    con = getconnection(sysname)

                Catch vaEx As vaCustomException
                    vaEx.AddTrace(mClassName, "getData")
                    Throw vaEx
                Catch ex As Exception
                    Throw New vaCustomException(ex, mClassName, "getData", "Unable to connect server - " + CStr(sysname))
                    GoTo nx
                End Try
                Try
                    da.Merge(getsData(sysname, xdoc, con))
                Catch vaEx As vaCustomException
                    vaEx.AddTrace(mClassName, "getData")
                    Throw vaEx
                Catch ex As Exception
                    Throw New vaCustomException(ex, mClassName, "getData", "Unable to get data from server - " + CStr(sysname))
                    GoTo nx
                Finally
                    con.Close()
                    con.Dispose()
                    con = Nothing
                End Try
nx:         Next
            'con = Nothing
            'xnodelistf = Nothing
            'xnodelistc = Nothing
            'xnodelista = Nothing
            getData = da
        End Function
        Private Function getsData(ByVal sysName As String, ByVal xDoc As XmlDocument, ByVal con As WqlConnectionManager) As DataSet 'Implements Global.visAd.Framework.vaInterface.getData
            Dim ds As New System.Data.DataSet
            Dim dTable As DataTable
            Dim patchTable As DataTable
            Dim fields As New ArrayList
            Dim fieldattr As New ArrayList
            Dim xl As New Xml.XmlDocument
            Dim i As Integer
            Dim sq As String
            sq = ""
            xl = xDoc
            Dim j = 0
            Dim k = 0
            'ds = New DataSet
            '-----------------------Checking if sysName supplied is blank-----------------------
            If IsDBNull(sysName) Or sysName = "" Then
                getsData = ds
                Exit Function
            End If
            '------------------------Get fields in arraylist--------------------------------
            fields = getVisField(xl, "SCCM")
            For i = 0 To fields.Count - 1
                fieldattr = getVisFieldAttr(xl, "SCCM", fields(i))  'Get field attributes---
                sq = getWqlQuery(fields(i), fieldattr)              'Get SQL String



                If sq <> "" Then
                    Dim bool As Boolean
                    bool = False
                    sq = LCase(sq)
                    Dim sqlInj1 = InStr(sq, "delete")
                    sqlInj1 = InStr(sq, "truncate")
                    sqlInj1 = InStr(sq, "drop")
                    sqlInj1 = InStr(sq, "delete")
                    sqlInj1 = InStr(sq, "update")
                    sqlInj1 = InStr(sq, "insert")
                    sqlInj1 = InStr(sq, "table")

                    If sqlInj1 <> 0 Then bool = True
                    If bool = True Then
                        Dim ex As New Exception("Invalid Query in provider configuration file")
                        Throw ex
                    End If
                    '----------------create table dynamically---------------
                    Try '1----------
                        dTable = New DataTable(("SCCM-") + CStr(fields(i)))
                        For j = 0 To col_name.Count - 1
                            Dim colm As DataColumn
                            colm = New DataColumn(col_name(j))
                            colm.DataType = System.Type.GetType("System.String")
                            dTable.Columns.Add(colm)
                        Next
                    Catch vaEx As vaCustomException
                        vaEx.AddTrace(mClassName, "getsData")
                        Throw vaEx
                    Catch ex As Exception
                        Throw New vaCustomException(ex, mClassName, "getsData", "Error while creating Data table")
                    End Try

                    '---------------query connection object for sql statment----------
                    Dim rquery As IResultObject
                    Dim rValue As IResultObject
                    Try  '2--------


                        rquery = con.QueryProcessor.ExecuteQuery(sq)

                    Catch vaEx As vaCustomException
                        vaEx.AddTrace(mClassName, "getsData")
                        Throw vaEx
                    Catch ex As Exception
                        Throw New vaCustomException(ex, mClassName, "getsData", "Unable to query data from server " + CStr(sq))
                    End Try
                    '---------------Fill table with values dynamically-------------------
                    Try
                        Dim PS As String
                        For Each rValue In rquery
                            Dim dRow As DataRow
                            dRow = dTable.NewRow()
                            Dim m = 0
                            For k = 0 To col_sname.Count - 1
                                If rValue(col_sname(k)).IsArray = True Then
                                    Dim pa() As String
                                    pa = rValue(col_sname(k)).StringArrayValue
                                    If pa Is Nothing Then
                                        PS = "Not Available"
                                    Else
                                        PS = pa(0)
                                    End If
                                Else
                                    PS = rValue(col_sname(k)).StringValue
                                End If
                                If IsDBNull(PS) Then PS = "Not Available"
                                dRow.Item(col_name(m).ToString) = PS
                                m = m + 1
                            Next

                            dTable.Rows.Add(dRow)
                            dRow = Nothing

                        Next
                    Catch vaEx As vaCustomException
                        vaEx.AddTrace(mClassName, "getsData")
                        Throw vaEx
                    Catch ex As Exception
                        Throw New vaCustomException(ex, mClassName, "getsData", "Unable to populate data in table '" + CStr(dTable.TableName) + "'")
                    End Try

                    If (dTable.Rows.Count = 0) Then
                        dTable.Rows.Add("No data found")
                    End If
                    ds.Tables.Add(dTable)
                    dTable = Nothing
                    'rValue = Nothing
                    'rquery = Nothing

                End If
            Next
            xl = Nothing
            Dim xnodelist As XmlNodeList
            Dim xnode As XmlNode
            xnodelist = xDoc.SelectNodes("/visAd/va-servertype[@type='SCCM']/va-filter/va-field")
            For Each xnode In xnodelist
                'If (xnode.Attributes.GetNamedItem("fieldname").Value = "SAMPLEPATCHINFO") Then
                '    samplepatchTable = getSamplePatchInfo()
                '    ds.Tables.Add(samplepatchTable.Copy())

                'End If
                If (xnode.Attributes.GetNamedItem("fieldname").Value = "PATCHINFO") Then
                    patchTable = getPatchInfo(con)
                    ds.Tables.Add(patchTable.Copy())

                End If
            Next

            getsData = ds

        End Function
        'Public Property vaDataset() As System.Data.DataSet 'Implements Global.visAd.Framework.vaInterface.vaDataset
        '    Get
        '        vaDataset = ds
        '    End Get
        '    Set(ByVal value As System.Data.DataSet)
        '        ds = vaDataset
        '    End Set
        'End Property
        Private Function getVisServer(ByVal xDoc As XmlDocument, ByVal Servertype As String) As ArrayList
            '------------------get all serverIds from visAd xml--------------------

            Dim ar As New ArrayList
            Dim xnodelist As XmlNodeList
            Dim xnode As XmlNode
            Dim i = 0
            Try
                xnodelist = xDoc.SelectNodes("/visAd/va-servertype/va-servers")
                For Each xnode In xnodelist
                    If xnode.ParentNode.Attributes.GetNamedItem("type").Value = Servertype Then
                        ar.Add(xnode.Attributes.GetNamedItem("id").Value)
                    End If
                Next
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getVisServer")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getVisServer", "Unable to read Servers from visAd XML")
            End Try

            xnodelist = Nothing
            xnode = Nothing
            getVisServer = ar
        End Function
        Private Function getVisField(ByVal xDoc As XmlDocument, ByVal Servertype As String) As ArrayList
            '------------------get all fields from visAd xml----------------------
            Dim ar1 As New ArrayList
            Dim xnodelist As XmlNodeList
            Dim xnode As XmlNode
            Dim i = 0
            Try
                xnodelist = xDoc.SelectNodes("/visAd/va-servertype[@type='" + Servertype + "']/va-filter/va-field")
                For Each xnode In xnodelist
                    If (xnode.Attributes.GetNamedItem("fieldname").Value <> "SAMPLEPATCHINFO" And xnode.Attributes.GetNamedItem("fieldname").Value <> "PATCHINFO") Then
                        ar1.Add(xnode.Attributes.GetNamedItem("fieldname").Value)
                    End If

                Next
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getVisField")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getVisField", "Unable to read fields from visAd XML")
            End Try

            xnodelist = Nothing
            xnode = Nothing
            getVisField = ar1
        End Function
        Private Function getVisFieldAttr(ByVal xDoc As XmlDocument, ByVal Servertype As String, ByVal fieldName As String) As ArrayList
            '------------------get all fields attribute from visAd xml--------------------
            Dim ar As New ArrayList
            Dim xnodelist As XmlNodeList
            Dim xnode As XmlNode
            Dim i = 0
            Try
                xnodelist = xDoc.SelectNodes("/visAd/va-servertype[@type='" + Servertype + "']/va-filter/va-field[@fieldname='" + fieldName + "']/va-filterattribute")
                For Each xnode In xnodelist
                    ar.Add(xnode.Attributes.GetNamedItem("filtername").Value)
                Next
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getVisFieldAttr")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getVisFieldAttr", "Unable to read field attributes from visAd XML for field " + CStr(fieldName))
            End Try

            xnodelist = Nothing
            xnode = Nothing
            getVisFieldAttr = ar
        End Function

        Private Function getWqlQuery(ByVal field As String, ByVal fieldattr As ArrayList) As String
            '--------------query string generator funtion----------------------
            Dim j, col
            col = ""
            col_sname.Clear()
            col_name.Clear()
            '----------------------get config file-----------------------------

            Dim xl As New Xml.XmlDocument
            Dim pth = AppDomain.CurrentDomain.BaseDirectory

            Try
                pth = pth + ConfigurationManager.AppSettings("SCCMConfig")
                xl.Load(pth)
                '----------get node list to avoid memory overheads in function
                xnodelistf = xl.SelectNodes("/configuration/va-servertype[@type='SCCM']/va-field")
                xnodelistc = xl.SelectNodes("/configuration/va-servertype[@type='SCCM']/va-field")
                xnodelista = xl.SelectNodes("/configuration/va-servertype[@type='SCCM']/va-field[@fieldname='" + field + "']/va-filterattribute")

            Catch fex As FileNotFoundException
                Throw New vaCustomException(fex, mClassName, "getWqlQuery")

            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getWqlQuery")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getWqlQuery", "Unable to read internal configuration file")
            End Try

            '-------------check if field is supported-------------------------
            Dim tabl = getSupFieldValue(field)

            If tabl = "" Then
                ' MsgBox("'" + CStr(field) + "' field is not supported in current version")
                Dim ex As New Exception("'" + CStr(field) + "' field is not supported in current version")
                Throw ex
                col = ""
                getWqlQuery = col
                Exit Function
            End If
            '----------------loop for field attributes------------------------

            For j = 0 To fieldattr.Count - 1
                '--------------------get attributes of fields from config file----------
                Dim v = getSupFieldAttr(field, fieldattr.Item(j).ToString)
                If v = "" Then
                    Throw New vaCustomException("'" + CStr(field) + "-" + fieldattr.Item(j) + "' attribute not supported")
                    'GoTo nex
                End If
                If v <> "" Then
                    col_sname.Add(v)
                    col_name.Add(fieldattr.Item(j))
                    col = col + v + ","             'columns are getting added
                End If
nex:        Next
            If col <> "" Then
                col = Mid(col, 1, Len(col) - 1)
            End If
            '-----------------get field table name in sql-------------------------------
            Try
                If tabl <> "" Then
                    col = "select " + col + " from " + tabl
                    Dim sq = getSupFieldCriteria(field)
                    If sq <> "" Then col = col + CStr(sq)
                End If
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getWqlQuery")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getWqlQuery", "Unable to create query string")
            End Try

            getWqlQuery = col
            xl = Nothing
        End Function

        Private Function getSupFieldValue(ByVal field As String) As String
            '---------------------check function for supported fields----------------
            Dim ar1 As String
            Dim ret As String
            Dim xnode As XmlNode
            Try
                Dim i = 0
                ret = ""
                For Each xnode In xnodelistf
                    ar1 = xnode.Attributes.GetNamedItem("fieldname").Value
                    If ar1 = field Then
                        ret = xnode.Attributes.GetNamedItem("value").Value
                        Exit For
                    End If
                Next
                getSupFieldValue = ret
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getSupFieldValue")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getSupFieldValue", "Error while retriving " + CStr(field) + "' from config file")
            End Try

            xnode = Nothing
        End Function
        Private Function getSupFieldCriteria(ByVal field As String) As String
            '----------------------check function for supported field criteria-(only one)----------
            Dim ar1 As String
            Dim ret As String

            Dim xnode As XmlNode
            Dim i = 0
            Try
                ret = ""
                For Each xnode In xnodelistc
                    ar1 = xnode.Attributes.GetNamedItem("fieldname").Value
                    If ar1 = field Then
                        ret = xnode.Attributes.GetNamedItem("criteria").Value
                        Exit For
                    End If
                Next
                If ret <> "" Then

                    xnode = Nothing
                    getSupFieldCriteria = " " + CStr(ret)
                Else

                    xnode = Nothing
                    getSupFieldCriteria = CStr(ret)
                End If
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getSupFieldCriteria")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getSupFieldCriteria", "Unable to get sub query string")
            End Try

        End Function
        Private Function getSupFieldAttr(ByVal field As String, ByVal attr As String) As String
            '--------------check function for supported field attribute------------------
            Dim ar As String
            Dim ret = ""
            Dim xnode As XmlNode
            Dim i = 0
            Try
                For Each xnode In xnodelista
                    ar = xnode.Attributes.GetNamedItem("filtername").Value
                    If ar = attr Then
                        ret = xnode.Attributes.GetNamedItem("value").Value
                        Exit For
                    End If
                Next
            Catch vaEx As vaCustomException
                vaEx.AddTrace(mClassName, "getSupFieldAttr")
                Throw vaEx
            Catch ex As Exception
                Throw New vaCustomException(ex, mClassName, "getSupFieldAttr", "Unable to get field '" + CStr(field) + "' attributes")
            End Try

            xnode = Nothing
            getSupFieldAttr = ret

        End Function
        Private Function getPatchInfo(ByVal con As WqlConnectionManager) As DataTable
            Dim dt As New DataTable("SCCM-PATCHINFO")
            Dim sql As String
            Dim count As Int16
            Dim rquery As IResultObject
            Dim rValue As IResultObject
            Dim innerValue As IResultObject
            Dim dr As DataRow
            Dim resource As String
            Dim resources As New ArrayList
            Dim resourceDetails As DataRow()
            Dim dTable As New DataTable
            Dim drFinal As DataRow
            Dim flag As Boolean
            Dim innerData As String

            dt.Columns.Add("Computer Name")
            dt.Columns.Add("IP Address")
            dt.Columns.Add("Operating System")
            'dt.Columns.Add("Status")
            dt.Columns.Add("LastState")
            dt.Columns.Add("Remaining Patches-KBArticle#")

            ' sql = "select distinct SMS_R_System.ResourceNames, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.IPAddresses, SMS_G_System_PATCHSTATE.ID, SMS_G_System_PATCHSTATE.Status from  SMS_R_System inner join SMS_G_System_PATCHSTATE on SMS_G_System_PATCHSTATE.ResourceID = SMS_R_System.ResourceId"
            sql = "select distinct SMS_R_System.ResourceNames, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.IPAddresses, SMS_G_System_PatchStatusEx.QNumbers, SMS_G_System_PATCHSTATUSEX.LastState from  SMS_R_System inner join SMS_G_System_PATCHSTATUSEX on SMS_G_System_PATCHSTATUSEX.ResourceID = SMS_R_System.ResourceId"
            If (con.IsQueryValid(sql)) Then


                Try
                    rquery = con.QueryProcessor.ExecuteQuery(sql)

                    For Each rValue In rquery

                        dr = dt.NewRow()
                        For Each innerValue In rValue.GenericsArray
                            For Each innerData In innerValue.PropertyNames
                                Select Case innerData
                                    Case "ResourceNames"
                                        If innerValue("ResourceNames").IsArray = True Then
                                            Dim pa() As String
                                            pa = innerValue("ResourceNames").StringArrayValue
                                            If (pa.Length > 0) Then
                                                dr("Computer Name") = pa(0)
                                            Else
                                                dr("Computer Name") = "Not Available"
                                            End If
                                        Else
                                            dr("Computer Name") = innerValue("ResourceNames").StringValue
                                        End If
                                        If (Not resources.Contains(dr("Computer Name").ToString())) Then
                                            resources.Add(dr("Computer Name").ToString())
                                        End If

                                    Case "OperatingSystemNameandVersion"
                                        If innerValue("OperatingSystemNameandVersion").IsArray = True Then
                                            Dim pa() As String
                                            pa = innerValue("OperatingSystemNameandVersion").StringArrayValue
                                            If (pa.Length > 0) Then
                                                dr("Operating System") = pa(0)
                                            Else
                                                dr("Operating System") = "Not Available"
                                            End If
                                        Else
                                            dr("Operating System") = innerValue("OperatingSystemNameandVersion").StringValue
                                        End If

                                    Case "IPAddresses"
                                        If innerValue("IPAddresses").IsArray = True Then
                                            Dim pa() As String
                                            pa = innerValue("IPAddresses").StringArrayValue
                                            If (pa.Length > 0) Then
                                                dr("IP Address") = pa(0)
                                            Else
                                                dr("IP Address") = "Not Available"
                                            End If
                                        Else
                                            dr("IP Address") = innerValue("IPAddresses").StringValue
                                        End If

                                    Case "QNumbers"
                                        If innerValue("QNumbers").IsArray = True Then
                                            Dim pa() As String
                                            pa = innerValue("QNumbers").StringArrayValue
                                            If (pa.Length > 0) Then
                                                If (String.IsNullOrEmpty(pa(0))) Then
                                                    pa(0) = "000000"
                                                End If
                                                dr("Remaining Patches-KBArticle#") = pa(0)
                                            Else
                                                dr("Remaining Patches-KBArticle#") = "Not Available"
                                            End If
                                        Else
                                            If (String.IsNullOrEmpty(innerValue("QNumbers").StringValue)) Then
                                                dr("Remaining Patches-KBArticle#") = "000000"
                                            Else
                                                dr("Remaining Patches-KBArticle#") = innerValue("QNumbers").StringValue
                                            End If

                                        End If

                                        'Case "Status"
                                        'If innerValue("Status").IsArray = True Then
                                        'Dim pa() As String
                                        ' pa = innerValue("Status").StringArrayValue
                                        ' dr("Status") = pa(0)
                                        ' Else
                                        ' dr("Status") = innerValue("Status").StringValue

                                    Case "LastState"
                                        If innerValue("LastState").IsArray = True Then
                                            Dim pa() As String
                                            pa = innerValue("LastState").StringArrayValue
                                            If (pa.Length > 0) Then
                                                dr("LastState") = pa(0)
                                            Else
                                                dr("LastState") = "Not Available"
                                            End If
                                        Else
                                            dr("LastState") = innerValue("LastState").StringValue
                                        End If

                                End Select
                            Next
                        Next
                        dt.Rows.Add(dr)
                    Next

                Catch vaEx As vaCustomException
                    vaEx.AddTrace(mClassName, "getPatchInfo")
                    Throw vaEx
                Catch ex As Exception
                    Throw New vaCustomException(ex, mClassName, "getPatchInfo", "Unable to query data from server ")
                End Try


                dTable = dt.Clone()

                For Each resource In resources
                    resourceDetails = dt.Select("[Computer Name] = '" + resource + "'")
                    drFinal = dTable.NewRow()
                    flag = False
                    Dim ids As New StringBuilder
                    For count = 0 To resourceDetails.Length - 1
                        'If (resourceDetails(count)("LastState").ToString() = "0" Or resourceDetails(count)("LastState").ToString() = "3" Or resourceDetails(count)("LastState").ToString() = "4") Then
                        If (resourceDetails(count)("LastState").ToString() <> "105") Then
                            flag = True
                            ids.Append(resourceDetails(count)("Remaining Patches-KBArticle#").ToString())
                            ids.Append(", ")
                        End If
                    Next
                    If (ids.Length > 2) Then
                        ids.Remove(ids.Length - 2, 2)
                    End If
                    drFinal("Computer Name") = resourceDetails(resourceDetails.Length - 1)("Computer Name").ToString()
                    drFinal("Operating System") = resourceDetails(resourceDetails.Length - 1)("Operating System").ToString()
                    drFinal("IP Address") = resourceDetails(resourceDetails.Length - 1)("IP Address").ToString()
                    drFinal("Remaining Patches-KBArticle#") = ids.ToString()
                    If (flag) Then
                        drFinal("LastState") = "Updates Needed"
                    Else
                        drFinal("LastState") = "Updated"
                    End If
                    dTable.Rows.Add(drFinal)
                Next
            Else
                Throw New vaCustomException("Invalid query")
            End If
            If (dTable.Rows.Count = 0) Then
                dTable.Rows.Add("No data found")
            End If
            getPatchInfo = dTable
            'getPatchInfo = dt
        End Function

        Public Function GetSchema() As String
            Dim serverFields As New XmlDocument
            Dim filepath As String
            Dim fieldNodeList As XmlNodeList
            Dim fieldNode As XmlNode
            Dim fieldAttribute, filterAttribute As XmlAttribute
            Dim fieldName As String
            Dim filterNode As XmlNode
            Dim filterName As String

            filepath = AppDomain.CurrentDomain.BaseDirectory + ConfigurationManager.AppSettings("SCCMConfig")
            serverFields.Load(filepath)
            fieldNodeList = serverFields.SelectNodes("/configuration/va-servertype/va-field")
            For Each fieldNode In fieldNodeList
                fieldName = fieldNode.Attributes.GetNamedItem("fieldname").Value
                fieldNode.Attributes.RemoveAll()
                fieldAttribute = serverFields.CreateAttribute("fieldname")
                fieldAttribute.Value = fieldName
                fieldNode.Attributes.Append(fieldAttribute)
                For Each filterNode In fieldNode.ChildNodes
                    filterName = filterNode.Attributes.GetNamedItem("filtername").Value
                    filterNode.Attributes.RemoveAll()
                    filterAttribute = serverFields.CreateAttribute("filtername")
                    filterAttribute.Value = filterName
                    filterNode.Attributes.Append(filterAttribute)
                Next

            Next

            GetSchema = serverFields.OuterXml

        End Function
        'Private Function getSamplePatchInfo() As DataTable
        '    Dim filepath As String
        '    filepath = AppDomain.CurrentDomain.BaseDirectory + ConfigurationManager.AppSettings("SamplePatchFilePath")
        '    Dim dt As New DataTable()

        '    Dim ds1 As New DataSet()
        '    ds1.ReadXml(filepath)

        '    dt = ds1.Tables(0)
        '    dt.TableName = "SCCM-SAMPLEPATCHINFO"
        '    If (dt.Rows.Count = 0) Then
        '        dt.Rows.Add("No data found")
        '    End If
        '    getSamplePatchInfo = dt
        'End Function
    End Class
End Namespace
